import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

my_course_change_router = APIRouter(prefix="/school/CourseChange", tags=["调课管理"],
                                    dependencies=[Depends(oauth2_scheme)])


class CourseChangeSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fclassno: Optional[str] = Field("0", description="班级编号，默认0")
    fbegdate: Optional[str] = Field("2000-01-01", description="调课开始日期，默认2000-01-01")
    fenddate: Optional[str] = Field("2000-01-01", description="调课结束日期，默认2000-01-01")


@my_course_change_router.get("/list", summary="")
def list_my_course_change(search: CourseChangeSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_tc_coursechange b,a_ts_class c,a_ts_course d,a_ts_teacher e"
    sql = sql + " where 1=1"
    sql = sql + " and b.fclass_no=c.fclass_no"
    sql = sql + " and b.fplancourse_no=d.fcourse_no"
    sql = sql + " and b.fplanteacher_no=e.fteacher_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if ((search.fbegdate != "") and (search.fenddate != "")):
        sql = sql + " and a.fplandate between :fbegdate and :fenddate "
        param["fbegdate"] = search.fbegdate
        param["fenddate"] = search.fenddate
    if search.fclassno != "":
        sql = sql + " and b.fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select b.fbillno,b.fsite_no,b.fpoint_no,b.fclass_no,b.fplandate,"
    sql = sql + " b.fplanweek,b.fplantime,b.fplancourse_no,b.fplanteacher_no,"
    sql = sql + " b.frealdate,b.frealweek,b.frealtime,b.frealcourse_no,b.frealteacher_no,"
    sql = sql + " b.fnote,b.fstate,b.fupddate,b.foperator"
    sql = sql + " c.fclass_name,d.fcourse_name,e.fteacher_name"
    sql = sql + " from a_tc_coursechange b,a_ts_class c,a_ts_course d,a_ts_teacher e"
    sql = sql + " where 1=1"
    sql = sql + " and b.fclass_no=c.fclass_no"
    sql = sql + " and b.fplancourse_no=d.fcourse_no"
    sql = sql + " and b.fplanteacher_no=e.fteacher_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if ((search.fbegdate != "") and (search.fenddate != "")):
        sql = sql + " and a.fplandate between :fbegdate and :fenddate "
        param["fbegdate"] = search.fbegdate
        param["fenddate"] = search.fenddate
    if search.fclassno != "":
        sql = sql + " and b.fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    if search.order == "desc":
        sql = sql + " order by  b.fbillno desc "
    else:
        sql = sql + " order by  b.fbillno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fpointno'] = data[key][2]
        d1['fclassno'] = data[key][3]
        d1['fplandate'] = data[key][4]
        d1['fplanweek'] = data[key][5]
        d1['fplantime'] = data[key][6]
        d1['fplancourseno'] = data[key][7]
        d1['fplanteacherno'] = data[key][8]
        d1['frealdate'] = data[key][9]
        d1['frealweek'] = data[key][10]
        d1['frealtime'] = data[key][11]
        d1['frealcourseno'] = data[key][12]
        d1['frealteacherno'] = data[key][13]
        d1['fnote'] = data[key][14]
        d1['fstate'] = data[key][15]
        d1['fupddate'] = data[key][16]
        d1['foperator'] = data[key][17]
        d1['fclassname'] = data[key][18]
        d1['fcoursename'] = data[key][19]
        d1['fteachername'] = data[key][20]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
